package sanrenxing.Paper.dao.impl;

import sanrenxing.Paper.dao.SubjectDao;
import sanrenxing.Paper.dao.SubjectDao;
import sanrenxing.Paper.domain.Subject;
import sanrenxing.utils.JdbcUtils;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 功能：
 * 编写者：阳赛
 * 日期：
 * 版本：1.0
 */
public class SubjectDaoImpl implements SubjectDao {
    /**
     * 将实体对象添加到数据库subjects表中
     * @param subject 题库对象
     * @return 成功添加的行数(1 : 添加成功 ; 0 : 添加失败)
     */
    @Override
    public int add(Subject subject) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="insert into subjects values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        //实例化预编译语句对象
        pstmt=conn.prepareStatement(sql);
        //设置SQL语句中占位符赋值
        pstmt.setInt(1, subject.getSubjectId());
        pstmt.setString(2, subject.getSubjectType());
        pstmt.setInt(3, subject.getCourseId());
        pstmt.setString(4, subject.getStem());
        pstmt.setString(5, subject.getOptionA());
        pstmt.setString(6, subject.getOptionB());
        pstmt.setString(7, subject.getOptionC());
        pstmt.setString(8, subject.getOptionD());
        pstmt.setString(9, subject.getOptionE());
        pstmt.setString(10, subject.getOptionF());
        pstmt.setString(11, subject.getOptionG());
        pstmt.setString(12, subject.getOptionH());
        pstmt.setString(13, subject.getCorrect());
        pstmt.setInt(14, subject.getHard());
        //使用预编译语句对象执行SQL语句，返回影响的行数
        rows=pstmt.executeUpdate();
    } catch (
    SQLException e) {
        e.printStackTrace();
        //抛出运行时异常
        throw new RuntimeException(e.getMessage(),e);
    } finally {
        //关闭/释放资源
        JdbcUtils.close(null,pstmt,conn);
    }
        //返回影响的行数
        return rows;
    }

    /**
     * 根据题库编号主键从数据库subjects表中删除指定题库数据
     * @param subjectId 题库编号主键
     * @return 删除影响的行数(1 : 删除成功 ; 0 : 删除失败)
     */
    @Override
    public int delete(int subjectId) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="delete from subjects where subjectId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setInt(1,subjectId);
            //使用预编译语句对象执行SQL语句，返回影响的行数
            rows=pstmt.executeUpdate();
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回影响的行数
        return rows;
    }

    /**
     * 将员工对象数据修改到数据库subjects表中
     * @param subject 题库对象
     * @return 修改影响的行数(1 : 修改成功 ; 0 : 修改失败)
     */
    @Override
    public int update(Subject subject) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //定义影响行数的变量
        int rows=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="update subjects set subjectId=?,subjectType=?,courseId=?,stem=?,optionA=?,optionB=?,optionC=?," +
                    "optionD=?,optionE=?,optionF=?,optionG=?,optionH=?,correct=?,hard=? where subjectId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setInt(1, subject.getSubjectId());
            pstmt.setString(2, subject.getSubjectType());
            pstmt.setInt(3, subject.getCourseId());
            pstmt.setString(4, subject.getStem());
            pstmt.setString(5, subject.getOptionA());
            pstmt.setString(6, subject.getOptionB());
            pstmt.setString(7, subject.getOptionC());
            pstmt.setString(8, subject.getOptionD());
            pstmt.setString(9, subject.getOptionE());
            pstmt.setString(10, subject.getOptionF());
            pstmt.setString(11, subject.getOptionG());
            pstmt.setString(12, subject.getOptionH());
            pstmt.setString(13, subject.getCorrect());
            pstmt.setInt(14, subject.getHard());
            //使用预编译语句对象执行SQL语句，返回影响的行数
            rows=pstmt.executeUpdate();
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回影响的行数
        return rows;
    }

    /**
     * 根据题库编号主键从数据库subject表中查找单个数据
     * @param subjectId 题库编号主键
     * @return 题库对象(如果返回null值 ， 表示查找失败)
     */
    @Override
    public Subject findById(String subjectId) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的题库对象
        Subject subject =null;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select subjectId,subjectType,courseId,stem,optionA,optionB,optionC,optionD,optionE,optionF," +
                    "optionG,optionH,correct,hard from subjects where subjectId=?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //设置SQL语句中占位符赋值
            pstmt.setString(1,subjectId);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //判断结果集，并把结果集的数据封装到题库对象中
            if(rs.next()){
                //实例化题库对象
                subject =new Subject();
                //把结果集数据赋值给题库对象各属性
                subject.setSubjectId(rs.getInt("subjectId"));
                subject.setSubjectType(rs.getString("subjectType"));
                subject.setCourseId(rs.getInt("courseId"));
                subject.setStem(rs.getString("stem"));
                subject.setOptionA(rs.getString("optionA"));
                subject.setOptionB(rs.getString("optionB"));
                subject.setOptionC(rs.getString("optionC"));
                subject.setOptionD(rs.getString("optionD"));
                subject.setOptionE(rs.getString("optionE"));
                subject.setOptionF(rs.getString("optionF"));
                subject.setOptionG(rs.getString("optionG"));
                subject.setOptionH(rs.getString("optionH"));
                subject.setCorrect(rs.getString("correct"));
                subject.setHard(rs.getInt("hard"));
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回题库对象
        return subject;
    }

    /**
     * 查询数据库subjects表中所有题库
     * @return 题库对象集合
     */
    @Override
    public List<Subject> findAll() {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的题库对象集合
        List<Subject> subjectList =new ArrayList<Subject>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select subjectId,subjectType,courseId,stem,optionA,optionB,optionC,optionD,optionE,optionF,optionG,optionH,correct,hard from subjects";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //循环判断结果集，并把结果集的数据封装到对象中
            while(rs.next()){
                //定义题库对象
                Subject subject =new Subject();
                //把结果集数据赋值给题库对象各属性
                subject.setSubjectId(rs.getInt("subjectId"));
                subject.setSubjectType(rs.getString("subjectType"));
                subject.setCourseId(rs.getInt("courseId"));
                subject.setStem(rs.getString("stem"));
                subject.setOptionA(rs.getString("optionA"));
                subject.setOptionB(rs.getString("optionB"));
                subject.setOptionC(rs.getString("optionC"));
                subject.setOptionD(rs.getString("optionD"));
                subject.setOptionE(rs.getString("optionE"));
                subject.setOptionF(rs.getString("optionF"));
                subject.setOptionG(rs.getString("optionG"));
                subject.setOptionH(rs.getString("optionH"));
                subject.setCorrect(rs.getString("correct"));
                subject.setHard(rs.getInt("hard"));
                //把题库对象添加题库集合中
                subjectList.add(subject);
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回题库对象集合
        return subjectList;
    }


    /**
     * 从数据库subjects表中查询指定页的题库对象集合
     *
     * @param rows  每页行数
     * @param pages 页数
     * @return 当前页的题库对象集合
     */
    @Override
    public List<Subject> findByPage(int rows, int pages) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的对象集合
        List<Subject> subjectList =new ArrayList<Subject>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select subjectId,subjectType,courseId,stem,optionA,optionB,optionC,optionD,optionE," +
                    "optionF,optionG,optionH,correct,hard from subjects limit ?,?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //给分页语句占位符赋值
            pstmt.setInt(1,(pages-1)*rows);
            pstmt.setInt(2,rows);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //循环判断结果集，并把结果集的数据封装到对象中
            while(rs.next()){
                //定义对象
                Subject subject =new Subject();
                //把结果集数据赋值给员工对象各属性
                subject.setSubjectId(rs.getInt("subjectId"));
                subject.setSubjectType(rs.getString("subjectType"));
                subject.setCourseId(rs.getInt("courseId"));
                subject.setStem(rs.getString("stem"));
                subject.setOptionA(rs.getString("optionA"));
                subject.setOptionB(rs.getString("optionB"));
                subject.setOptionC(rs.getString("optionC"));
                subject.setOptionD(rs.getString("optionD"));
                subject.setOptionE(rs.getString("optionE"));
                subject.setOptionF(rs.getString("optionF"));
                subject.setOptionG(rs.getString("optionG"));
                subject.setOptionH(rs.getString("optionH"));
                subject.setCorrect(rs.getString("correct"));
                subject.setHard(rs.getInt("hard"));
                //把题库对象添加题库集合中
                subjectList.add(subject);
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回对象集合
        return subjectList;
    }

    /**
     * 统计数据库subjects表中记录总数
     *
     * @return 移库表中记录总数
     */
    @Override
    public int count() {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回表中总数据行数
        int rowCount=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select count(*) as subjectscount from subjects";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集合
            rs=pstmt.executeQuery();
            //判断结果集，并获得总行数
            if(rs.next()){
                //如果统计行数时没有指定别名，则用数字0作为参数
                rowCount=rs.getInt("subjectscount");
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回表中数据总行数
        return rowCount;
    }

    /**
     * 条件分页查询
     *
     * @param rows      每页行数
     * @param pages     页数
     * @param condition 条件
     * @return 题库对象集合
     */
    @Override
    public List<Subject> findByPage(int rows, int pages, String condition) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回的题库对象集合
        List<Subject> subjectList =new ArrayList<Subject>();
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select subjectId,subjectType,courseId,stem,optionA,optionB,optionC,optionD,optionE," +
                    "optionF,optionG,optionH,correct,hard from subjects where 1=1 "+condition+" limit ?,?";
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //给分页语句占位符赋值
            //分页语句：select * from 表名 limits (pages-1)*rows,rows
            pstmt.setInt(1,(pages-1)*rows);
            pstmt.setInt(2,rows);
            //使用预编译语句对象执行SQL语句，返回结果集
            rs=pstmt.executeQuery();
            //循环判断结果集，并把结果集的数据封装到员工对象中
            while(rs.next()){
                //定义员工对象
                Subject subject =new Subject();
                //把结果集数据赋值给对象各属性
                subject.setSubjectId(rs.getInt("subjectId"));
                subject.setSubjectType(rs.getString("subjectType"));
                subject.setCourseId(rs.getInt("courseId"));
                subject.setStem(rs.getString("stem"));
                subject.setOptionA(rs.getString("optionA"));
                subject.setOptionB(rs.getString("optionB"));
                subject.setOptionC(rs.getString("optionC"));
                subject.setOptionD(rs.getString("optionD"));
                subject.setOptionE(rs.getString("optionE"));
                subject.setOptionF(rs.getString("optionF"));
                subject.setOptionG(rs.getString("optionG"));
                subject.setOptionH(rs.getString("optionH"));
                subject.setCorrect(rs.getString("correct"));
                subject.setHard(rs.getInt("hard"));
                //把题库对象添加题库集合中
                subjectList.add(subject);
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(rs,pstmt,conn);
        }
        //返回员工对象集合
        return subjectList;
    }

    /**
     * 条件统计
     *
     * @param condition 条件
     * @return 题目数
     */
    @Override
    public int count(String condition) {
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;
        //定义返回表中总数据行数
        int rowCount=0;
        try {
            //实例化数据库连接对象
            conn= JdbcUtils.getConnection();
            //编写SQL语句字符串
            String sql="select count(*) as subjectscount from subjects where 1=1 "+condition;
            //实例化预编译语句对象
            pstmt=conn.prepareStatement(sql);
            //使用预编译语句对象执行SQL语句，返回结果集合
            rs=pstmt.executeQuery();
            //判断结果集，并获得总行数
            if(rs.next()){
                //如果统计行数时没有指定别名，则用数字0作为参数
                rowCount=rs.getInt("subjectscount");
            }
        } catch (SQLException e) {
            //抛出运行时异常
            throw new RuntimeException(e.getMessage(),e);
        } finally {
            //关闭/释放资源
            JdbcUtils.close(null,pstmt,conn);
        }
        //返回表中数据总行数
        return rowCount;
    }

    @Override
    public String getCondition(Subject subject) {
        //定义查询条件字符串，初值为带空格的字符串(注意后续条件中的空格)
        String condition=" ";
        //如果对象为空，则直接返回空字符串
        if(subject ==null){
            return condition;
        }
        //试题编号
        if(subject.getSubjectId()!=0){
            condition += " and subjectId="+ subject.getSubjectId();
        }
        //试题类型
        if(subject.getSubjectType()!=null && subject.getSubjectType().trim().length()!=0){
            condition += " and subjectType like '%"+ subject.getSubjectType()+"%' ";
        }
        //科目，来自course表--外键
        if(subject.getCourseId()!=0){
            condition += " and courseId="+ subject.getCourseId();
        }
        //题干
        if(subject.getStem()!=null && subject.getStem().trim().length()!=0){
            condition += " and stem like '%"+ subject.getStem()+"%' ";
        }
        //选项A
        if(subject.getOptionA()!=null && subject.getOptionA().trim().length()!=0){
            condition += " and optionA like '%"+ subject.getOptionA()+"%' ";
        }
        //选项B
            if(subject.getOptionB()!=null && subject.getOptionB().trim().length()!=0){
            condition += " and optionB like '%"+ subject.getOptionB()+"%' ";
        }
        //选项C
        if(subject.getOptionC()!=null && subject.getOptionC().trim().length()!=0){
            condition += " and optionC like '%"+ subject.getOptionC()+"%' ";
        }
        //选项D
        if(subject.getOptionD()!=null && subject.getOptionD().trim().length()!=0){
            condition += " and optionD like '%"+ subject.getOptionD()+"%' ";
        }
        //选项E
        if(subject.getOptionE()!=null && subject.getOptionE().trim().length()!=0){
            condition += " and optionE like '%"+ subject.getOptionE()+"%' ";
        }
        //选项F
        if(subject.getOptionF()!=null && subject.getOptionF().trim().length()!=0){
            condition += " and optionF like '%"+ subject.getOptionF()+"%' ";
        }
        //选项G
        if(subject.getOptionG()!=null && subject.getOptionG().trim().length()!=0){
            condition += " and optio nG like '%"+ subject.getOptionG()+"%' ";
        }
        //选项H
        if(subject.getOptionH()!=null && subject.getOptionH().trim().length()!=0){
            condition += " and optionH like '%"+ subject.getOptionH()+"%' ";
        }
        //正确答案
        if(subject.getCorrect()!=null && subject.getCorrect().trim().length()!=0){
            condition += " and correct like '%"+ subject.getCorrect()+"%' ";
        }
        //难度系数
        if(subject.getHard()!=0){
            condition += " and hard="+ subject.getHard();
        }
        //返回条件字符串
        return condition;
    }
    @Override
    public ArrayList<Subject> getSubjects(int courseId , String subjectType) {
        ArrayList<Subject> subjectList =new ArrayList<Subject>();
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;

        try {
            conn=JdbcUtils.getConnection();
            String sql="select * from subjects where courseId=? and subjectType=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,courseId);
            pstmt.setString(2,subjectType);
            rs=pstmt.executeQuery();
            while (rs.next()){
                Subject subject =new Subject();
                subject.setSubjectId(rs.getInt("subjectId"));
                subject.setSubjectType(rs.getString("subjectType"));
                subjectList.add(subject);
            }
        } catch (SQLException e) {
            throw  new RuntimeException(e.getMessage(),e);
        }finally {
            JdbcUtils.close(rs,pstmt,conn);
        }
        return subjectList;

    }

    @Override
    public ArrayList<Subject> getSubjectss(int courseId, int hard , String subjectType) {
        ArrayList<Subject> subjectList =new ArrayList<Subject>();
        //声明数据库连接对象
        Connection conn=null;
        //声明预编译语句对象
        PreparedStatement pstmt=null;
        //声明结果集对象
        ResultSet rs=null;

        try {
            conn=JdbcUtils.getConnection();
            String sql="select * from subjects where courseId=? and hard=? and subjectType=? ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,courseId);
            pstmt.setInt(2,hard);
            pstmt.setString(3,subjectType);
            rs=pstmt.executeQuery();

            while (rs.next()){
                Subject subject =new Subject();
                subject.setSubjectId(rs.getInt("subjectId"));
                subject.setSubjectType(rs.getString("subjectType"));
                subject.setHard(rs.getInt("hard"));
                subjectList.add(subject);
            }

        } catch (SQLException e) {
            throw  new RuntimeException(e.getMessage(),e);
        }finally {
            JdbcUtils.close(rs,pstmt,conn);
        }
        return subjectList;
    }
}

